<?php
/**
 * @file class.db.php
 * @package news
 * @author LD
 * @desc 数据库操作
 */

/**
 * 活动公告数据库操作
 *
 */
class kyodb{
	
	/**
	 * 数据操作对象
	 */
	private $dbh;
	
	/**
	 * 构造函数，初始化数据对象
	 *
	 */
	public function __construct(){
		try{ 
			$this->dbh = &ADONewConnection('mysqli');
			$this->dbh->Connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
			$this->dbh->debug = false;
			if (!$this->dbh){
				die("(error: 1011)MySQL Conneting Failed!");
			}
			$this->dbh->Execute('set names '.DB_CHARSET.';');
		}catch(Exception $e){
   			die( "(error: 1012)MySQL Conneting Failed: " . $e->getMessage() );
		}
	}//END func __construct
	
	/**
	 * 析构函数，回收资源
	 *
	 */
	public function __destruct(){
		if (!$this->dbh)
			$this->dbh->Close();
	}//END func __destruct
	
	/**
	 * 插入活动公告,返回公告ID
	 *
	 * @param array $post
	 * @return int $ID
	 */
	public function insertPost($post){
		if (empty($post)) return 0;
		
		if (empty($post['author_id'])) $post['author_id'] = 1;
		if (empty($post['date'])) $post['date'] = date('Y-m-d H:i:s',time());
		$post['modified'] = $post['date'];
		if (empty($post['status'])) $post['status'] = 'publish';
		if (empty($post['order'])) $post['order'] = 0;
		if (empty($post['category_id'])) $post['category_id'] = 1;
		
		if ( empty($post['type']) ) {
			$cat = $this->getCategories( $post['category_id'] );
			if ( empty($cat[0]['type']) )
				$post['type'] = 'news';
			else $post['type'] = $cat[0]['type'];
		}
		
		$query = "INSERT INTO `".DB_NAME."`.`".TABLE_PREFIX."news` 
			(`ID`, `author_id`, `category_id`, `title`, `content`, `date`, `modified`, `status`, `type`, `order`) 
			VALUES (NULL, '{$post['author_id']}', '{$post['category_id']}', '{$post['title']}', 
			 '{$post['content']}', '{$post['date']}', '{$post['modified']}', '{$post['status']}', 
			 '{$post['type']}', '{$post['order']}');";
		
	/*	$update = "UPDATE `".DB_NAME."`.`".TABLE_PREFIX."news_categories` SET `count` = `count` + 1 
			WHERE `".TABLE_PREFIX."news_categories`.`ID` = {$post['category_id']} LIMIT 1 ;";*/
		try {
			//var_dump($query);
			$this->dbh->StartTrans();
			$this->dbh->Execute($query);
			$ID = $this->dbh->Insert_ID();
			//$this->dbh->Execute($update);
			$this->dbh->CompleteTrans();
			if ($this->dbh->HasFailedTrans()) {
				$this->dbh->FailTrans();
				die( "(error: 1031)Fransaction insertPost({$post['title']}) Failed: " . $e->getMessage() );
			}elseif (!empty($ID) and $ID>0){
					return $ID;
			}			
		}catch (Exception $e){
			die( "(error: 1032)Function insertPost({$post['title']}) Failed: " . $e->getMessage() );
		}
		return 0;
	}//END func insertPost
	
	/**
	 * 插入分类，返回内容ID
	 *
	 * @param array $cat
	 * @return int $ID
	 */
	public function insertCategory($cat){
		if (empty($cat)) return 0;
		
		if (empty($cat['date'])) $cat['date'] = date('Y-m-d H:i:s',time());

		$query = "INSERT INTO `".DB_NAME."`.`".TABLE_PREFIX."news_categories` (`ID`, `name`, `desc`, `date`) VALUES 
			 (NULL, '{$cat['name']}', '{$cat['desc']}', '{$cat['date']}');";
		try {
			$this->dbh->StartTrans();
			$this->dbh->Execute($query);
			$ID = $this->dbh->Insert_ID();
			$this->dbh->CompleteTrans();
			if ($this->dbh->HasFailedTrans()) {
				$this->dbh->FailTrans();
				die( "(error: 1041)Fransaction insertCategory({$cat['name']}) Failed: " . $e->getMessage() );
			}elseif (!empty($ID) and $ID>0) {
					return $ID;
			}		
		}catch (Exception $e){
			die( "(error: 1042)Function insertCategory({$cat['name']}) Failed: " . $e->getMessage() );
		}		
		return 0;
	}//END func insertCategory
	
	/**
	 * 批量插入公告tags，返回true
	 *
	 * @param int $nid
	 * @param array $tags
	 * @return Boolean
	 */
	public function insertTags($nid, $tags){
		if ( empty($nid) or empty($tags) )
			return false;
		$date = date('Y-m-d H:i:s',time());
		$query = "INSERT INTO `".DB_NAME."`.`".TABLE_PREFIX."news_tags` (`name`, `new_id`, `date`) VALUES ";
			 
		$sum = count($tags)-1;
		for ($i=0; $i<$sum; $i++){
			$query .= "('{$tags[$i]}', '{$nid}', '{$date}'), ";
		}
		$query .= "('{$tags[$i]}', '{$nid}', '{$date}') ;";

		try {
			$this->dbh->StartTrans();
			$this->dbh->Execute($query);
			if ($this->dbh->HasFailedTrans()) {
				$this->dbh->FailTrans();
				die( "(error: 1051)Fransaction insertTags({$nid}) Failed: " . $e->getMessage() );
			}else {
				return true;
			}		
				
		}catch (Exception $e){
			die( "(error: 1052)Function insertTags({$nid}) Failed: " . $e->getMessage() );
		}
		return false;
	}//END func insertTags
	
	/**
	 * 插入作者，返回作者ID
	 *
	 * @param array $cat
	 * @return int $ID
	 */
	public function insertAuthor($auh){
		if (empty($auh)) return 0;
		
		if (empty($auh['date'])) $auh['date'] = date('Y-m-d H:i:s',time());
		if (empty($auh['name'])) $auh['name'] = $auh['nick'];
		
		$query = "INSERT INTO `".DB_NAME."`.`".TABLE_PREFIX."news_authors` (`ID`, `nick`, `name`, `desc`, `date`) 
			 VALUES (NULL, '{$auh['nick']}', '{$auh['name']}', '{$auh['desc']}', '{$auh['date']}' );";
		try {
			$this->dbh->StartTrans();
			$this->dbh->Execute($query);
			$ID = $this->dbh->Insert_ID();
			$this->dbh->CompleteTrans();
			if ($this->dbh->HasFailedTrans()) {
				$this->dbh->FailTrans();
				die( "(error: 1041)Fransaction insertAuthor({$auh['name']}) Failed: " . $e->getMessage() );
			}elseif (!empty($ID) and $ID > 0) {
					return $ID;
			}		
		}catch (Exception $e){
			die( "(error: 1042)Function insertAuthor({$auh['name']}) Failed: " . $e->getMessage() );
		}		
		return 0;
	}//END func insertAuthor
	
	/**
	 * 从数据中得到一篇文章
	 * 
	 * @param int $ID 文章的ID号
	 * @return array
	 */
	public function getPost($ID){
		$query = "SELECT * FROM `".DB_NAME."`.`".TABLE_PREFIX."news` WHERE `ID` = {$ID} LIMIT 1";
		try {
			$re = $this->dbh->Execute($query);
			if ($re){
				$post = $re->FetchRow();
				return $post;
			}
		}catch (Exception $e){
			die( "(error: 1052)Function getPost({$ID}) Failed: " . $e->getMessage() );
		}	
		return null;
	}//END func getPost

	/**
	 * 返回文章对应的关键字
	 * 
	 * @param int $ID 文章的关键字
	 * @return String
	 */
	public function getTag($ID){
		$query = "SELECT * FROM `".DB_NAME."`.`".TABLE_PREFIX."news_tags` WHERE `new_id` = {$ID} LIMIT 1";
		try {
			$re = $this->dbh->Execute($query);
			if ($re){
				$post = $re->FetchRow();
				return $post;
			}
		}catch (Exception $e){
			die( "(error: 1062)Function getPost({$ID}) Failed: " . $e->getMessage() );
		}	
		return null;
	}//END func getTag
	
	/**
	 * 返回所有分类
	 * 
	 * @return array
	 */
	public function getCategories($ID = 0){
		if ($ID == 0)
			$query = "SELECT * FROM `".DB_NAME."`.`".TABLE_PREFIX."news_categories` ";
		else 
			$query = "SELECT * FROM `".DB_NAME."`.`".TABLE_PREFIX."news_categories` WHERE `ID` = '{$ID}' LIMIT 1";
			
		try {
			$re = $this->dbh->Execute($query);
			if ($re){
				$cats = array();
				while (!$re->EOF){
					array_push($cats, $re->fields);
					$re->MoveNext();
				}	
				return $cats;
			}
		}catch (Exception $e){
			die( "(error: 1072)Function getCategories() Failed: " . $e->getMessage() );
		}	
		return null;
	}//END func getCategories
	
	/**
	 * 返回所有发布者
	 * 
	 * @return array
	 */
	public function getAuthors(){
		$query = "SELECT * FROM `".DB_NAME."`.`".TABLE_PREFIX."news_authors` ";
		try {
			$re = $this->dbh->Execute($query);
			if ($re){
				$auhs = array();
				while (!$re->EOF){
					array_push($auhs,$re->fields );
					$re->MoveNext();
				}	
				return $auhs;
			}
		}catch (Exception $e){
			die( "(error: 1082)Function getAuthors() Failed: " . $e->getMessage() );
		}	
		return null;
	}//END func getAuthors
	
	/**
	 * 更新文章xTag
	 */
	public function updateNews( $ID, $type ,$post ){
		$data = date('Y-m-d H:i:s',time());
		switch ($type){
			case 'tag': 
				$query = "UPDATE `".DB_NAME."`.`".TABLE_PREFIX."news_tags` SET `name` = '{$post[0]}', `date` = '{$data}' WHERE 
			 			`".TABLE_PREFIX."news_tags`.`new_id` = {$ID} LIMIT 1 ;";
			 	break;
			
			case 'post':
				$cat = $this->getCategories( $post['category_id'] );
				if ( empty($cat[0]['type']) )
					$post['type'] = 'news';
				else $post['type'] = $cat[0]['type'];
				$query = "UPDATE `".DB_NAME."`.`".TABLE_PREFIX."news` SET `author_id` = '{$post['author_id']}',
					`category_id` = '{$post['category_id']}', `title` = '{$post['title']}', `content` = '{$post['content']}',
					 `type` = '{$post['type']}', `modified` = '{$data}' WHERE `".TABLE_PREFIX."news`.`ID` = {$ID} LIMIT 1 ; ";
				break;
			
			case 'cat':
				$query = "UPDATE `".DB_NAME."`.`".TABLE_PREFIX."news_categories` SET `name` = '{$post['name']}',
					`desc` = '{$post['desc']}', `date` = '{$data}' 
					 WHERE `".TABLE_PREFIX."news_categories`.`ID` = {$ID} LIMIT 1 ;";
				break;
		}//end switch
		
		try {
			$this->dbh->Execute($query);
		}catch (Exception $e){
			die( "(error: 1092)Function getCategories() Failed: " . $e->getMessage() );
		}	
	}//END func updateTags
	
	/**
	 * 从数据中得到许多文章
	 * 
	 * @param int $limit
	 * @return array
	 */
	public function getPosts($limit, $condi = ''){
		$query = "SELECT `ID`,`category_id`,`title`, DATE_FORMAT( `modified` , '%y-%m-%d' ) AS date 
			FROM `".DB_NAME."`.`".TABLE_PREFIX."news` {$condi} ORDER BY `ID` DESC LIMIT {$limit}, 10";
		//var_dump($query);
		try {
			$re = $this->dbh->Execute($query);
			if ($re){
				$posts = array();
				while (!$re->EOF){
					array_push($posts, $re->fields);
					$re->MoveNext(); 
				}
				return $posts;
			}
		}catch (Exception $e){
			die( "(error: 1102)Function getPosts({$limit}) Failed: " . $e->getMessage() );
		}	
		return null;
	}//END func getPosts
	
	public function getSum($type){
		if ($type == 'post')
			$query = "SELECT count( `ID` ) AS sum FROM `".DB_NAME."`.`".TABLE_PREFIX."news` ";
		elseif ($type == 'image')
			$query = "SELECT count( `ID` ) AS sum FROM `".DB_NAME."`.`".TABLE_PREFIX."news_images` ";
			
		$re = $this->dbh->Execute($query);
		return (int)$re->fields['sum'];
	}
	
	/**
	 * 删除公告
	 * 
	 * @param int $ID
	 * @return boolean
	 */
	public function deleteNews($ID, $type, $path=null){
		if ($type == 'post')
			$query = "DELETE FROM `".DB_NAME."`.`".TABLE_PREFIX."news` WHERE `".TABLE_PREFIX."news`.`ID` = {$ID} LIMIT 1";
		elseif ($type == 'cat')
			$query = "DELETE FROM `".DB_NAME."`.`".TABLE_PREFIX."news_categories` WHERE `".TABLE_PREFIX."news_categories`.`ID` = {$ID} LIMIT 1";
		elseif ($type == 'image')
			$query = "DELETE FROM `".DB_NAME."`.`".TABLE_PREFIX."news_images` WHERE `".TABLE_PREFIX."news_images`.`ID` = {$ID} LIMIT 1";
		try {
			$re = $this->dbh->Execute($query);
			if ($type == 'post'){
				$query = "DELETE FROM `".DB_NAME."`.`".TABLE_PREFIX."news_tags` WHERE `".TABLE_PREFIX."news_tags`.`ID` = {$ID} LIMIT 1";
				$re = $this->dbh->Execute($query);				
			}elseif($type == 'image'){
				$root_path = dirname(dirname(_SITE_ROOT));
				@unlink( $root_path.DIRECTORY_SEPARATOR.$path );
			}
			
			if ($re) return true;
		}catch (Exception $e){
			die( "(error: 1112)Function deleteNews({$ID}, '{$type}') Failed: " . $e->getMessage() );
		}	
		return false;
	}//END func deleteNews
	
	/**
	 * 插入一张图片
	 */
	public function insertImage($url, $desc){
		//$data = date('Y-m-d H:i:s',time());
		$query = "INSERT INTO `".DB_NAME."`.`".TABLE_PREFIX."news_images` ( `ID` , `url` , `desc`, `date` ) VALUES (
				NULL , '{$url}', '{$desc}', NOW() ); ";
		try {
			$this->dbh->Execute($query);
			$ID = $this->dbh->Insert_ID();
			if ( $ID>0 ) return $ID;
		}catch (Exception $e){
			die( "(error: 1122)Function insertImage({$url}) Failed: " . $e->getMessage() );
		}
		return 0;
	}//END func insertImage
	
	
	/**
	 * 得到图片列表
	 *  
	 * @return array
	 */
	public function getImages($limit){
		$query = "SELECT * FROM `".DB_NAME."`.`".TABLE_PREFIX."news_images` ORDER BY `ID` DESC LIMIT {$limit}, 10";
		try {
			$re = $this->dbh->Execute($query);
			if ($re){
				$auhs = array();
				while (!$re->EOF){
					array_push($auhs,$re->fields );
					$re->MoveNext();
				}	
				return $auhs;
			}
		}catch (Exception $e){
			die( "(error: 1132)Function getImages() Failed: " . $e->getMessage() );
		}	
		return null;
	}//END func getAuthors
	
} //END class kyodb
?>